﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using DapperExtensions;
using BLevy.Datos.Interfases;
using BLevy.Entidad;

namespace BLevy.Datos.Repositories
{

    public interface ISocioRepository : IRepository<Socio>
    {
        IList<Socio> FindByCodigoOrApellidoOrDocumento(string apellido, string documento, int? nrosocio);
        IList<Socio> FindAllConLocalidadZonaPlan();
        Socio FindByIdConLocalidadZonaPlanFormaPago(int id);
        int SocioBaja(int id, SocioBaja baja);
        int SocioReactivar(int id, SocioBaja reactivar);
        IList<Socio> FindAllActivos();
        long FindNextNumeroCertificado();
        int VerificarNumeroCertificado(long numero_certificado);
        int RegistrarMensaje(int socio_id, string mensaje);
        IList<Socio> FindAllConLocalidadZonaPlanFiltro(string nroSocio, string documento, string apellido, string nombres, int? localidad_id, int? zona_id, int? formapago_id);
        IList<Socio> FindAllFiltro(int? localidad_id, int? activo);
        IList<Socio> FindAllConAdherentesZona(int? activo);
        IList<Socio> FindAllConAdherentesPorMesCumpleaños(int mes);
        IList<Socio> FindBajasAutomaticas(DateTime fecha_baja);
        IList<Socio> FindAllConLocalidadPlanByNroCertificadoByZona(string nro_cert_desde, string nro_cert_hasta, int? localidad_id, int? zona_id);
        IList<Socio> FindAllActivosConLocalidadZonaPlan();
        IList<SocioBaja> FindAllBajasFiltro(int? localidad_id, int? zona_id, DateTime? desde, DateTime? hasta);
        Socio FindByCertificado(int certificado);
        IList<SocioStruct> FindAllFiltro(string nroSocio, string documento, string apellido, string nombres, int? localidad_id, int? zona_id, int? formapago_id, int? dosperiodos, int? tresperiodos);
        
    }


    public class SocioRepository : ISocioRepository
    {

        protected readonly IDbConnection _db;

        public SocioRepository()
        {
            try
            {
                _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString());
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public IList<Socio> FindAll()
        {
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio>("SELECT * FROM SOCIOS").ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IQueryable<Socio> Find(System.Linq.Expressions.Expression<Func<Socio, bool>> predicate)
        {
            throw new NotImplementedException();
        }

        public Socio FindById(int id)
        {
            string query = "SELECT * FROM SOCIOS WHERE ID = @id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio>(query, new { id = id }).SingleOrDefault();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public Socio FindByCertificado(int certificado)
        {
            string query = "SELECT * FROM SOCIOS WHERE NUMERO_CERTIFICADO = @certificado";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio>(query, new { certificado = certificado }).SingleOrDefault();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public int Add(Socio newEntity)
        {
            int socio_id = 0;
            string query = "INSERT INTO SOCIOS (NUMERO_CERTIFICADO, APELLIDO, " +
                                  "NOMBRES, DOCUMENTO, FECHA_NACIMIENTO, FECHA_INGRESO, FECHA_VENCIMIENTO, " +
                                  "FECHA_ULTIMOPAGO, VIGENCIA, EDAD, DIRECCION, LOCALIDAD_ID, ZONA_ID, " +
                                  "TELEFONO, PAMI, EMAIL, CONVENIO, ADICIONAL, PLAN_ID, FORMAPAGO_ID, " +
                                  "OBSERVACIONES, ACTIVO, FECHA_POSIBLECOBRO, RECARGO, BONIFICACION, CUOTAFIJA)" +
                          "VALUES (@numero_certificado, @apellido, @nombres, @documento, @fecha_nacimiento, @fecha_ingreso, @fecha_vencimiento, " +
                                  "@fecha_ultimopago, @vigencia, @edad, @direccion, @localidad_id, @zona_id, " +
                                  "@telefono, @pami, @email, @convenio, @adicional, @plan_id, @formapago_id, " +
                                  "@observaciones, @activo, @fecha_posiblecobro, @recargo, @bonificacion, @cuotafija); " +
                                  "SELECT LAST_INSERT_ID();";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    socio_id = _db.Query<int>(query, new
                    {
                        numero_certificado = newEntity.numero_certificado,
                        apellido = newEntity.apellido,
                        nombres = newEntity.nombres,
                        documento = newEntity.documento,
                        direccion = newEntity.direccion,
                        zona_id = newEntity.zona_id,
                        fecha_nacimiento = newEntity.fecha_nacimiento,
                        fecha_ingreso = newEntity.fecha_ingreso,
                        fecha_vencimiento = newEntity.fecha_vencimiento,
                        fecha_ultimopago = newEntity.fecha_ultimopago,
                        vigencia = newEntity.vigencia,
                        edad = newEntity.edad,
                        telefono = newEntity.telefono,
                        observaciones = newEntity.observaciones,
                        plan_id = newEntity.plan_id,
                        localidad_id = newEntity.localidad_id,
                        pami = newEntity.pami,
                        email = newEntity.email,
                        convenio = newEntity.convenio,
                        adicional = newEntity.adicional,
                        formapago_id = newEntity.formapago_id,
                        activo = newEntity.activo,
                        fecha_posiblecobro = newEntity.fecha_posiblecobro,
                        recargo = newEntity.recargo,
                        bonificacion = newEntity.bonificacion,
                        cuotafija = newEntity.cuotafija
                    }).Single();
                }
                return socio_id;
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int Remove(int id)
        {
            string query = "UPDATE SOCIOS SET ACTIVO = 0 " +
                           "WHERE ID = @id";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new { id = id });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Socio> FindByCodigoOrApellidoOrDocumento(string apellido, string documento, int? nrosocio)
        {
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    var result = _db.Query<Socio>("SELECT * FROM SOCIOS").ToList();

                    var salida = from row in result
                                 select row;

                    if (apellido != null)
                        salida = salida.Where(a => a.apellido.Contains(apellido));
                    if (documento != null)
                        salida = salida.Where(a => a.documento.Contains(documento));
                    if (nrosocio != null)
                        salida = salida.Where(a => a.id == nrosocio);

                    return salida.ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }

        }

        public IList<Socio> FindAllConLocalidadZonaPlan()
        {
            string query = "SELECT * FROM SOCIOS " +
                "INNER JOIN LOCALIDADES ON LOCALIDADES.ID = SOCIOS.LOCALIDAD_ID " +
                "INNER JOIN ZONAS ON ZONAS.ID = SOCIOS.ZONA_ID " +
                "INNER JOIN PLANES ON PLANES.ID = SOCIOS.PLAN_ID " +
                "ORDER BY SOCIOS.FECHA_INGRESO DESC";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio, Localidad, Zona, Plan, Socio>(query,
                        (socio, localidad, zona, plan) =>
                        {
                            socio.localidad = localidad;
                            socio.zona = zona;
                            socio.plan = plan;
                            return socio;
                        }, splitOn: "id").ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Socio> FindAllActivosConLocalidadZonaPlan()
        {
            string query = "SELECT * FROM SOCIOS " +
                "INNER JOIN LOCALIDADES ON LOCALIDADES.ID = SOCIOS.LOCALIDAD_ID " +
                "INNER JOIN ZONAS ON ZONAS.ID = SOCIOS.ZONA_ID " +
                "INNER JOIN PLANES ON PLANES.ID = SOCIOS.PLAN_ID " +
                "ORDER BY SOCIOS.FECHA_INGRESO DESC";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio, Localidad, Zona, Plan, Socio>(query,
                        (socio, localidad, zona, plan) =>
                        {
                            socio.localidad = localidad;
                            socio.zona = zona;
                            socio.plan = plan;
                            return socio;
                        }, splitOn: "id").ToList();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public Socio FindByIdConLocalidadZonaPlanFormaPago(int id)
        {
            string query = "SELECT * FROM SOCIOS " +
                "WHERE ID = @id";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio>(query, new { id = id }).SingleOrDefault();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int SocioBaja(int id, SocioBaja baja)
        {
            int resultado = 0;
            int resultado_socio_baja = 0;
            int resultado_adherente_baja = 0;

            string query = "UPDATE SOCIOS SET ACTIVO = 0 WHERE ID = @socio_id";

            string query_adherentes = "UPDATE ADHERENTES SET ACTIVO = 0 " +
                                      "WHERE SOCIO_ID = @socio_id";

            string query_baja_socio = "INSERT INTO SOCIOSBAJAS (SOCIO_ID, FECHA, MOTIVO, OPERACION, TIPO_BAJA) " +
                    "VALUES (@socio_id, @fecha, @motivo, @operacion, @tipo_baja)";

            string query_baja_adherentes = "INSERT INTO ADHERENTESBAJAS (ADHERENTE_ID, FECHA, MOTIVO, OPERACION, TIPO_BAJA) " +
                    "VALUES (@adherente_id, @fecha, @motivo, @operacion, @tipo_baja)";


            using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
            {
                _db.Open();
                IDbTransaction transaction = _db.BeginTransaction();

                try
                {

                    resultado = _db.Execute(query, new { socio_id = id }, transaction);
                    if (resultado > 0)
                    {
                        AdherenteRepository _repository = new AdherenteRepository();
                        IList<Adherente> adherentes = _repository.FindByIdSocioActivos(id);
                        if (adherentes.Count != 0)
                        {
                            int resultado_adherentes = _db.Execute(query_adherentes, new { socio_id = id }, transaction);
                            foreach (Adherente adherente in adherentes)
                            {
                                resultado_adherente_baja = _db.Execute(query_baja_adherentes, new { adherente_id = adherente.id, fecha = baja.fecha, motivo = baja.motivo, operacion = baja.operacion, tipo_baja = "A" }, transaction);
                            }
                            resultado_socio_baja = _db.Execute(query_baja_socio, new { socio_id = id, fecha = baja.fecha, motivo = baja.motivo, operacion = baja.operacion, tipo_baja = baja.tipo_baja }, transaction);
                            transaction.Commit();
                        }
                        else
                        {
                            resultado_socio_baja = _db.Execute(query_baja_socio, new { socio_id = id, fecha = baja.fecha, motivo = baja.motivo, operacion = baja.operacion, tipo_baja = baja.tipo_baja }, transaction);
                            transaction.Commit();
                        }
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    throw e;
                }
            }

            return resultado_socio_baja;
        }

        public int SocioReactivar(int id, SocioBaja reactivar)
        {
            int resultado = 0;
            int resultado_socio_reactivar = 0;
            int resultado_adherente_reactivar = 0;
            int resDeuda = 0;

            string query = "UPDATE SOCIOS SET ACTIVO = 1 WHERE ID = @socio_id";

            string query_adherentes = "UPDATE ADHERENTES a, adherentesbajas b SET a.ACTIVO = 1 " +
                                "WHERE a.SOCIO_ID = @socio_id and a.id IN (SELECT B.ID FROM (SELECT MAX(ID) ID, ADHERENTE_ID FROM ADHERENTESBAJAS GROUP BY ADHERENTE_ID) X " +
                                "INNER JOIN ADHERENTESBAJAS B ON X.ID = B.ID WHERE OPERACION = 'B' AND TIPO_BAJA = 'A')";

            string query_reactivar_socio = "INSERT INTO SOCIOSBAJAS (SOCIO_ID, FECHA, MOTIVO, OPERACION, TIPO_BAJA) " +
                    "VALUES (@socio_id, @fecha, @motivo, @operacion, @tipo_baja)";

            string query_reactivar_adherentes = "INSERT INTO ADHERENTESBAJAS (ADHERENTE_ID, FECHA, MOTIVO, OPERACION, TIPO_BAJA) " +
                    "VALUES (@adherente_id, @fecha, @motivo, @operacion, @tipo_baja)";

            string query_tipo_baja = "select * from sociosbajas where socio_id = @socio_id AND OPERACION = 'B' order by fecha desc limit 1";

            bool reactivo = false;

            using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
            {
                _db.Open();
                IDbTransaction transaction = _db.BeginTransaction();

                try
                {

                    resultado = _db.Execute(query, new { socio_id = id }, transaction);
                    if (resultado > 0)
                    {
                        AdherenteRepository _repository = new AdherenteRepository();
                        IList<Adherente> adherentes = _repository.FindByIdSocioInactivos(id);
                        if (adherentes.Count != 0)
                        {
                            int resultado_adherentes = _db.Execute(query_adherentes, new { socio_id = id }, transaction);
                            foreach (Adherente adherente in adherentes)
                            {
                                resultado_adherente_reactivar = _db.Execute(query_reactivar_adherentes, new { adherente_id = adherente.id, fecha = reactivar.fecha, motivo = reactivar.motivo, operacion = reactivar.operacion, tipo_baja = "A" }, transaction);

                                if (!(resultado_adherente_reactivar > 0))
                                {
                                    break;                                    
                                }
                            }

                            if (resultado_adherente_reactivar > 0)
                            {
                                resultado_socio_reactivar = _db.Execute(query_reactivar_socio, new { socio_id = id, fecha = reactivar.fecha, motivo = reactivar.motivo, operacion = reactivar.operacion, tipo_baja = reactivar.tipo_baja }, transaction);                                
                            }
                        }
                        else
                        {
                            resultado_socio_reactivar = _db.Execute(query_reactivar_socio, new { socio_id = id, fecha = reactivar.fecha, motivo = reactivar.motivo, operacion = reactivar.operacion, tipo_baja = reactivar.tipo_baja }, transaction);
                        }

                        if (resultado_socio_reactivar > 0)
                        {
                            //CALCULAR DEUDA SI LA BAJA FUE AUTOMATICA
                            Entidad.SocioBaja _socioBaja = _db.Query<SocioBaja>(query_tipo_baja, new { socio_id = id }).Single();

                            if (_socioBaja.tipo_baja.Equals("A"))
                            {
                                double deuda;

                                Socio _socio = FindById(_socioBaja.socio_id);
                                _socio.adherentes = adherentes;

                                PeriodoSocioRepository _repositorioPeriodo = new PeriodoSocioRepository();
                                double cuota = _repositorioPeriodo.CalcularCuota(_socio);

                                int mesesInactivos = Math.Abs((DateTime.Now.Month - _socioBaja.fecha.Month) + 12 * (DateTime.Now.Year - _socioBaja.fecha.Year));
                                deuda = cuota * mesesInactivos;

                                DeudaRepository _repositorioDeuda = new DeudaRepository();
                                Deuda _deuda = _repositorioDeuda.ConsultarDeudaPorSocio(_socio);
                                if (_deuda != null)
                                {
                                    deuda += _deuda.saldo;
                                    _deuda.saldo = deuda;

                                    resDeuda = _repositorioDeuda.ActualizarDeuda(_deuda);
                                }
                                else
                                {
                                    CobroRepository _repositorioCobro = new CobroRepository();
                                    Cobro _cobro = _repositorioCobro.FindLastCobroPorAfiliado(_socio.id);

                                    _deuda = new Deuda();

                                    _deuda.cobro_id = _cobro.id;
                                    _deuda.fecha = DateTime.Now;
                                    _deuda.saldo = deuda;
                                    _deuda.estado = "I";

                                    resDeuda = _repositorioDeuda.RegistrarDeuda(_deuda);
                                }
                                

                                if (resDeuda > 0)
                                {
                                    reactivo = true;
                                }
                            }
                        }
                    }

                    if (reactivo == true)
                    {
                        transaction.Commit();
                    }
                    else
                    {
                        transaction.Rollback();
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    throw e;
                }
            }

            return resDeuda;
        }

        public IList<Socio> FindAllActivos()
        {
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio>("SELECT * FROM SOCIOS WHERE ACTIVO = 1").ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public long FindNextNumeroCertificado()
        {
            string query = "SELECT MAX(NUMERO_CERTIFICADO) FROM SOCIOS";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    long numero = _db.Query<long>(query).Single();
                    if (numero == 0)
                    {
                        numero = 1;
                    }
                    else
                    {
                        numero = numero + 1;
                    }
                    return numero;
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int VerificarNumeroCertificado(long numero_certificado)
        {
            string query = "SELECT COUNT(*) FROM SOCIOS " +
                "WHERE NUMERO_CERTIFICADO = @numero";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<int>(query, new { numero = numero_certificado }).Single();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int Modify(Socio entity)
        {
            string query = "UPDATE SOCIOS SET NUMERO_CERTIFICADO = @numero_certificado, APELLIDO = @apellido, NOMBRES = @nombres, DOCUMENTO = @documento, FECHA_NACIMIENTO = @fecha_nacimiento, " +
               "FECHA_INGRESO = @fecha_ingreso, FECHA_VENCIMIENTO = @fecha_vencimiento, FECHA_ULTIMOPAGO = @fecha_ultimopago, VIGENCIA = @vigencia, " +
               "EDAD = @edad, DIRECCION = @direccion, LOCALIDAD_ID = @localidad_id, ZONA_ID = @zona_id, TELEFONO = @telefono, PAMI = @pami, EMAIL = @email, " +
               "CONVENIO = @convenio, ADICIONAL = @adicional, PLAN_ID = @plan_id, FORMAPAGO_ID = @formapago_id, OBSERVACIONES = @observaciones , " +
               "ACTIVO = @activo, FECHA_POSIBLECOBRO = @fecha_posiblecobro, RECARGO = @recargo, BONIFICACION = @bonificacion, CUOTAFIJA = @cuotafija " +
               "WHERE ID = @id";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new
                    {
                        numero_certificado = entity.numero_certificado,
                        apellido = entity.apellido,
                        nombres = entity.nombres,
                        documento = entity.documento,
                        fecha_nacimiento = entity.fecha_nacimiento,
                        fecha_ingreso = entity.fecha_ingreso,
                        fecha_vencimiento = entity.fecha_vencimiento,
                        fecha_ultimopago = entity.fecha_ultimopago,
                        vigencia = entity.vigencia,
                        edad = entity.edad,
                        direccion = entity.direccion,
                        localidad_id = entity.localidad_id,
                        zona_id = entity.zona_id,
                        telefono = entity.telefono,
                        pami = entity.pami,
                        email = entity.email,
                        convenio = entity.convenio,
                        adicional = entity.adicional,
                        plan_id = entity.plan_id,
                        formapago_id = entity.formapago_id,
                        observaciones = entity.observaciones,
                        activo = entity.activo,
                        fecha_posiblecobro = entity.fecha_posiblecobro,
                        recargo = entity.recargo,
                        bonificacion = entity.bonificacion,
                        cuotafija = entity.cuotafija,
                        id = entity.id
                    });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public int RegistrarMensaje(int socio_id, string mensaje)
        {
            string query = "UPDATE SOCIOS SET OBSERVACIONES = @mensaje " +
                " WHERE ID = @id";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Execute(query, new
                    {
                        id = socio_id,
                        mensaje = mensaje
                    });
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Socio> FindAllConLocalidadZonaPlanFiltro(string nroSocio, string documento, string apellido, string nombres, int? localidad_id, int? zona_id, int? formapago_id)
        {
            string query = "SELECT * " + 
               "FROM SOCIOS " +
               "INNER JOIN LOCALIDADES ON LOCALIDADES.ID = SOCIOS.LOCALIDAD_ID " +
               "INNER JOIN ZONAS ON ZONAS.ID = SOCIOS.ZONA_ID " +
               "INNER JOIN PLANES ON PLANES.ID = SOCIOS.PLAN_ID " +
               "INNER JOIN FORMASPAGO ON FORMASPAGO.ID = SOCIOS.FORMAPAGO_ID " + 
               "WHERE ((SOCIOS.NUMERO_CERTIFICADO = @nrosocio ) OR (@nrosocio IS NULL)) " +
               "AND ((SOCIOS.DOCUMENTO = @documento) OR (@documento IS NULL)) " + 
               "AND ((SOCIOS.APELLIDO LIKE CONCAT('%', @apellido, '%')) OR (@apellido IS NULL)) " + 
               "AND ((SOCIOS.NOMBRES LIKE CONCAT('%', @nombres, '%')) OR (@nombres IS NULL)) " +
               "AND ((SOCIOS.LOCALIDAD_ID = @localidad_id) OR (@localidad_id IS NULL)) " + 
               "AND ((SOCIOS.ZONA_ID = @zona_id) OR (@zona_id IS NULL)) " + 
               "AND ((SOCIOS.FORMAPAGO_ID = @formapago_id) OR (@formapago_id IS NULL)) " + 
               "ORDER BY SOCIOS.FECHA_INGRESO DESC LIMIT 100";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio, Localidad, Zona, Plan, FormaPago, Socio>(query,
                        (socio, localidad, zona, plan, formapago) =>
                        {
                            socio.localidad = localidad;
                            socio.zona = zona;
                            socio.plan = plan;
                            socio.formapago = formapago;
                            return socio;
                        }, new
                        {
                            nrosocio = nroSocio,
                            documento = documento,
                            apellido = apellido,
                            nombres = nombres,
                            localidad_id = localidad_id,
                            zona_id = zona_id,
                            formapago_id = formapago_id
                        }, splitOn: "id").ToList();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }


        public IList<Socio> FindAllFiltro(int? localidad_id, int? activo)
        {
            string query = "SELECT * FROM SOCIOS " +
                "WHERE ((SOCIOS.LOCALIDAD_ID = @localidad_id) OR (@localidad_id IS NULL)) " +
                        "AND ((SOCIOS.ACTIVO = @activo) OR (@activo IS NULL))";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio>(query, new
                    {
                        localidad_id = localidad_id,
                        activo = activo
                    }).ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }


        public IList<Socio> FindAllConAdherentesZona(int? activo)
        {
            IAdherenteRepository _repository = new AdherenteRepository();

            string query = "SELECT * FROM SOCIOS " +
                "INNER JOIN ZONAS ON ZONAS.ID = SOCIOS.ZONA_ID " + 
                "WHERE ((SOCIOS.ACTIVO = @activo) OR (@activo IS NULL)); " +               
                "SELECT * FROM ADHERENTES ";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    _db.Open();
                    using (var multi = _db.QueryMultiple(query, new { activo = activo }))
                    {
                        var socios = multi.Read<Socio, Zona, Socio>(
                            (socio, zona) => { socio.zona = zona; return socio; }, splitOn: "id").ToList();

                        var adherentes = multi.Read<Adherente>().ToList();

                        var adherentebysocioid = adherentes.ToLookup(a => a.socio_id);

                        foreach (var item in socios)
                        {
                            item.adherentes = adherentebysocioid[item.id].ToList();
                        }

                        return socios;
                    }


                }
            }
            catch(Exception e)
            {
                throw e;
            }
                        

        }


        public IList<Socio> FindAllConAdherentesPorMesCumpleaños(int mes)
        {
            IAdherenteRepository _repository = new AdherenteRepository();

            string query = @"SELECT * FROM SOCIOS  
                 WHERE (MONTH(FECHA_NACIMIENTO) = @mes);
                 SELECT * FROM ADHERENTES 
                 WHERE (MONTH(FECHA_NACIMIENTO) = @mes);";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    _db.Open();
                    using (var multi = _db.QueryMultiple(query, new { mes = mes }))
                    {
                        var socios = multi.Read<Socio>().ToList();

                        var adherentes = multi.Read<Adherente>().ToList();

                        var adherentebysocioid = adherentes.ToLookup(a => a.socio_id);

                        foreach (var item in socios)
                        {
                            item.adherentes = adherentebysocioid[item.id].ToList();
                        }

                        return socios;
                    }


                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        public IList<Socio> FindBajasAutomaticas(DateTime fecha_baja)
        {
            string query = "SELECT S.* FROM SOCIOS S " +
                            "LEFT JOIN SOCIOSBAJAS B ON S.ID = B.SOCIO_ID " +
                            "WHERE B.FECHA = @fecha_baja AND B.TIPO_BAJA = 'A'";
            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio>(query, new
                    {
                        fecha_baja = fecha_baja
                    }).ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }


        public IList<Socio> FindAllConLocalidadPlanByNroCertificadoByZona(string nro_cert_desde, string nro_cert_hasta, int? localidad_id, int? zona_id)
        {
            const string query = "SELECT * FROM SOCIOS " +
                "INNER JOIN LOCALIDADES ON LOCALIDADES.ID = SOCIOS.LOCALIDAD_ID " +
                "INNER JOIN ZONAS ON ZONAS.ID = SOCIOS.ZONA_ID " +
                "WHERE ((SOCIOS.NUMERO_CERTIFICADO BETWEEN @certificado_desde AND @certificado_hasta) OR ((@certificado_desde IS NULL) OR (@certificado_hasta IS NULL))) " +
                "AND ((SOCIOS.LOCALIDAD_ID = @localidad_id) OR (@localidad_id IS NULL)) " +
                "AND ((SOCIOS.ZONA_ID = @zona_id) OR (@zona_id IS NULL))";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<Socio, Localidad, Zona, Socio>(query,
                        (socio, localidad, zona) =>
                        {
                            socio.localidad = localidad;
                            socio.zona = zona;
                            return socio;
                        }, new
                        {
                            certificado_desde = nro_cert_desde,
                            certificado_hasta = nro_cert_hasta,
                            localidad_id = localidad_id,
                            zona_id = zona_id
                        }, splitOn: "id").ToList();
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }


        public IList<SocioBaja> FindAllBajasFiltro(int? localidad_id, int? zona_id, DateTime? desde, DateTime? hasta)
        {
            string query = "SELECT * FROM SOCIOSBAJAS " +
                "INNER JOIN SOCIOS ON SOCIOS.ID = SOCIOSBAJAS.SOCIO_ID " +
                "INNER JOIN ZONAS ON ZONAS.ID = SOCIOS.ZONA_ID " +  
                "WHERE ((SOCIOS.LOCALIDAD_ID = @localidad_id) OR (@localidad_id IS NULL)) " +
                "AND ((SOCIOS.ZONA_ID = @zona_id) OR (@zona_id IS NULL)) " + 
                "AND ((SOCIOSBAJAS.FECHA BETWEEN @desde AND @hasta) OR ((@desde IS NULL) OR (@hasta IS NULL))) " + 
                "ORDER BY SOCIOSBAJAS.FECHA DESC";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<SocioBaja, Socio, Zona, SocioBaja>(query,
                        (sociobaja, socio, zona) =>
                        {
                            sociobaja.socio = socio;
                            sociobaja.socio.zona = zona;
                            return sociobaja;
                        }, new
                        {
                            localidad_id = localidad_id,
                            zona_id = zona_id,
                            desde = desde,
                            hasta = hasta
                        }, splitOn: "id").ToList();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }



        public IList<SocioStruct> FindAllFiltro(string nroSocio, string documento, string apellido, string nombres, int? localidad_id, int? zona_id, int? formapago_id, int? dosperiodos, int? tresperiodos)
        {
            string query = "SELECT " + 
                "SOCIOS.ID, " +
                "SOCIOS.NUMERO_CERTIFICADO, " +
                "SOCIOS.FECHA_INGRESO, " +
                "SOCIOS.DOCUMENTO, " + 
                "SOCIOS.APELLIDO, " + 
                "SOCIOS.NOMBRES, " +
                "SOCIOS.FECHA_NACIMIENTO, " +
                "LOCALIDADES.ID AS LOCALIDAD_ID, " +
                "LOCALIDADES.LOCALIDAD, " +
                "ZONAS.ID AS ZONA_ID, " +
                "ZONAS.ZONA, " +
                "FORMASPAGO.ID AS FORMAPAGO_ID, " +
                "FORMASPAGO.FORMA_PAGO, " +
                "PLANES.ID AS PLAN_ID, " +
                "PLANES.NOMBRE AS PLAN, " +
                "SOCIOS.ACTIVO, " +
                "(SELECT COUNT(*) FROM PERIODOSSOCIOS WHERE PERIODOSSOCIOS.SOCIO_ID = SOCIOS.ID AND PERIODOSSOCIOS.ESTADO = 'I') AS PERIODOSIMPAGOS " + 
                "FROM SOCIOS " + 
                "INNER JOIN LOCALIDADES ON LOCALIDADES.ID = SOCIOS.LOCALIDAD_ID " +
                "INNER JOIN ZONAS ON ZONAS.ID = SOCIOS.ZONA_ID " +
                "INNER JOIN FORMASPAGO ON FORMASPAGO.ID = SOCIOS.FORMAPAGO_ID " +
                "INNER JOIN PLANES ON PLANES.ID = SOCIOS.PLAN_ID " +
               "WHERE ((NUMERO_CERTIFICADO = @nrosocio ) OR (@nrosocio IS NULL)) " +
               "AND ((DOCUMENTO = @documento) OR (@documento IS NULL)) " +
               "AND ((APELLIDO LIKE CONCAT('%', @apellido, '%')) OR (@apellido IS NULL)) " +
               "AND ((NOMBRES LIKE CONCAT('%', @nombres, '%')) OR (@nombres IS NULL)) " +
               "AND ((SOCIOS.LOCALIDAD_ID = @localidad_id) OR (@localidad_id IS NULL)) " +
               "AND ((SOCIOS.ZONA_ID = @zona_id) OR (@zona_id IS NULL)) " +
               "AND ((SOCIOS.FORMAPAGO_ID = @formapago_id) OR (@formapago_id IS NULL)) " +
               "AND (((SELECT COUNT(*) FROM PERIODOSSOCIOS WHERE PERIODOSSOCIOS.SOCIO_ID = SOCIOS.ID AND PERIODOSSOCIOS.ESTADO = 'I') = @dosperiodos) OR (@dosperiodos IS NULL)) " + 
               "AND (((SELECT COUNT(*) FROM PERIODOSSOCIOS WHERE PERIODOSSOCIOS.SOCIO_ID = SOCIOS.ID AND PERIODOSSOCIOS.ESTADO = 'I') >= @tresperiodos) OR (@tresperiodos IS NULL)) " + 
               "ORDER BY SOCIOS.FECHA_INGRESO DESC LIMIT 100";

            try
            {
                using (IDbConnection _db = new MySqlConnection(ConfigurationManager.ConnectionStrings["LEVYDB"].ToString()))
                {
                    return _db.Query<SocioStruct>(query, new
                        {
                            nrosocio = nroSocio,
                            documento = documento,
                            apellido = apellido,
                            nombres = nombres,
                            localidad_id = localidad_id,
                            zona_id = zona_id,
                            formapago_id = formapago_id,
                            dosperiodos = dosperiodos,
                            tresperiodos = tresperiodos
                        }).ToList();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }



    }
}
